<template>
  <div>
    <el-dialog v-if="enable" :visible.sync="enable" width="80%" custom-class="dialog-db-viewer" :close-on-click-modal="false">
      <div slot="title">
        <el-form :inline="true" class="demo-form-inline" size="mini">
          <el-form-item :label="`数据库 ${name}`"></el-form-item>
          <el-form-item label="数据表">
            <el-select placeholder="请选择" v-model="curTable" @change="actionActiveTable">
              <el-option v-for="table in tables" :key="table" :label="table" :value="table"></el-option>
            </el-select>
          </el-form-item>
          <el-form-item>
            <el-button-group style="margin-right:10px;">
              <el-button :disabled="null == curTable" :type="0!=condition.trim().length?'primary':null" @click="actionTableDataToggleConditionEdit"><i class="el-icon-aim"></i></el-button>
              <el-button :type="0!=condition.trim().length?'primary':null" v-if="conditionEditEnabel" @click="actionTableDataDoQuery"><i class="el-icon-check"></i></el-button>
            </el-button-group>

            <el-button-group style="margin-right:10px;">
              <el-button :disabled="null == curTable" :type="0!=order.trim().length?'primary':null" @click="actionTableDataToggleOrderEdit"><i class="el-icon-sort"></i></el-button>
              <el-button :type="0!=order.trim().length?'primary':null" v-if="orderEditEnable" @click="actionTableDataDoQuery"><i class="el-icon-check"></i></el-button>
            </el-button-group>
            
            <el-button size="mini" :disabled="!hasPrev" @click="actionTableDataPrevPage"><i class="el-icon-arrow-left"></i></el-button>
            <el-button size="mini" :disabled="!hasNext" @click="actionTableDataNextPage"><i class="el-icon-arrow-right"></i></el-button>
            <el-button size="mini" :disabled="null == curTable" @click="actionTableDataRefresh"><i class="el-icon-refresh"></i></el-button>
          </el-form-item>
        </el-form>
      </div>
      <div class="dialog-db-viewer-condition-text-editor mb-1" v-if="conditionEditEnabel">
        <codemirror v-model="condition" :options="conditionEditorOptions"></codemirror>
      </div>
      <div class="dialog-db-viewer-order-text-editor mb-1" v-if="orderEditEnable">
        <codemirror v-model="order" :options="conditionEditorOptions"></codemirror>
      </div>
      <div class="block-table-data">
        <table class="table-table-data">
          <thead>
            <tr v-if="tableData.length > 0">
              <th v-for="key in Object.keys(tableData[0])" :key="key">{{key}}</th>
            </tr>
            <tr v-if="tableData.length == 0">
              <th v-for="key in tableColumns" :key="key">{{key}}</th>
            </tr>
          </thead>
          <tbody>
            <tr v-for="(row, index) in tableData" :key="index">
              <td v-for="(item, cIndex) in row" :key="cIndex" @contextmenu="actionTableDataCellContextmenu($event, index, cIndex)">{{item}}</td>
            </tr>
          </tbody>
        </table>
      </div>
      <div class="text-right mt-1">
          数据量：{{curPage*pageSize}} - {{(curPage+1)*pageSize}} / {{dataCount}}
      </div>
    </el-dialog>

    <el-dialog title="数据编辑" :visible.sync="dataCellEditEnable" width="30%">
      <el-input v-model="dataCellEditValue"></el-input>
      <span slot="footer" class="dialog-footer">
        <el-button @click="dataCellEditEnable = false">取 消</el-button>
        <el-button type="primary" @click="actionDataCellEditSave">更新</el-button>
      </span>
    </el-dialog>
  </div>
</template>
<script>
import { codemirror } from 'vue-codemirror'
import "codemirror/theme/ambiance.css";
require("codemirror/mode/sql/sql.js");
export default {
    name : 'MysqlDatabaseViewDialog',
    components : {
        codemirror,
    },
    data() {
        return {
            name : null,
            connection : null,
            enable : false,
            tables : [],
            curTable : null,
            curPage : 0,
            tableData : [],
            tableColumns : [],
            dataCount : 0,
            pageSize : 100,
            conditionEditEnabel : false,
            condition : '',
            orderEditEnable : false,
            order : '',
            conditionEditorOptions : {
                mode:'sql',
                theme: 'ambiance',
                readOnly:false,
                lineNumbers:false,
                lineWrapping:true,
                autofocus:true,
            },

            // 单元编辑框
            dataCellEditEnable : false,
            dataCellEditValue : null,
            dataCellEditPosition : null,
        };
    },
    computed : {
        // 前一页判断
        hasPrev () {
            return this.curPage > 0;
        },
        // 下一页判断
        hasNext () {
            return (this.curPage+1) * this.pageSize < this.dataCount;
        },
    },
    methods : {
        // 打开弹框
        async open(connection, name) {
            this.name = name;
            this.connection = connection;
            await this.connection.execute(`use ${name}`);
            
            let queryTable = `SELECT TABLE_NAME from information_schema.\`TABLES\` WHERE TABLE_SCHEMA='${name}'`;
            console.log(queryTable);
            let tablelist = await this.connection.execute(queryTable);
            this.tables = [];
            for ( let i=0; i<tablelist.length; i++ ) {
                this.tables.push(tablelist[i]['TABLE_NAME']);
            }

            this.tableData = [];
            this.curTable = null;
            this.dataCount = 0;
            this.enable = true;
        },

        // 表格切换
        async actionActiveTable( table ) {
            this.curPage = 0;
            this.condition = '';
            this.order = '';
            this.conditionEditEnabel = false;
            this.orderEditEnable = false;
            await this.tableDataRefresh();
        },

        // 刷新表数据
        async tableDataRefresh() {
            let condition = (0 == this.condition.trim().length ) ? '' : `WHERE ${this.condition}`;
            let order = (0 == this.order.trim().length ) ? '' : `ORDER BY ${this.order}`;

            let dataCountQuery = `SELECT COUNT(1) AS TotalCount FROM \`${this.curTable}\` ${condition} ${order}`;
            let dataCount = null;
            try {
                dataCount = await this.connection.execute(dataCountQuery);
            } catch ( e ) {
                this.$message.error(e.message);
                return;
            }
            this.dataCount = dataCount[0].TotalCount;

            let offset = this.curPage * this.pageSize;
            let tableQuery = `SELECT * FROM \`${this.curTable}\` ${condition} ${order} LIMIT ${this.pageSize} OFFSET ${offset}`;
            console.log(tableQuery);
            this.tableData =  await this.connection.execute(tableQuery);
            
            this.tableColumns = [];
            if ( 0 == this.tableData.length ) {
                let columnQuery = `SELECT COLUMN_NAME from information_schema.\`COLUMNS\` WHERE TABLE_SCHEMA = '${this.name}' AND TABLE_NAME = '${this.curTable}'`;
                let columnData = await this.connection.execute(columnQuery);
                for ( let i=0; i<columnData.length; i++ ) {
                    this.tableColumns.push(columnData[i]['COLUMN_NAME']);
                }
            }
        },

        // 表数据前一页
        async actionTableDataPrevPage () {
            this.curPage --;
            await this.tableDataRefresh();
        },

        // 表数据后一页
        async actionTableDataNextPage () {
            this.curPage ++;
            await this.tableDataRefresh();
        },

        // 刷新表数据
        async actionTableDataRefresh() {
            await this.tableDataRefresh();
        },

        // 启用、关闭条件编辑
        actionTableDataToggleConditionEdit() {
            this.conditionEditEnabel = !this.conditionEditEnabel;
        },

        // 执行查询
        async actionTableDataDoQuery() {
            this.curPage = 0;
            await this.tableDataRefresh();
        },

        // 启用、关闭排序编辑
        actionTableDataToggleOrderEdit() {
            this.orderEditEnable = !this.orderEditEnable;
        },

        // 数据右键点击
        actionTableDataCellContextmenu(event, rowIndex, colIndex) {
            let $this = this;
            this.$contextmenu({ 
               items: [{
                    label: "编辑",
                    onClick: () => { $this.actionTableDataCellEdit(rowIndex, colIndex);},
                }],
                event,
                customClass: "custom-class",
                zIndex: 10000,
                minWidth: 230
            });
        },

        // 单元格编辑
        actionTableDataCellEdit(rowIndex, colIndex) {
            this.dataCellEditEnable = true;
            this.dataCellEditValue = this.tableData[rowIndex][colIndex]+'';
            this.dataCellEditPosition = {row:rowIndex, col:colIndex};
        },

        // 保存修改
        async actionDataCellEditSave() {
            let conditions = [];
            
            let conditionKeys = Object.keys(this.tableData[this.dataCellEditPosition.row]);
            for ( let i=0; i<conditionKeys.length; i++ ) {
                let key = conditionKeys[i];
                let value = this.connection.escape(this.tableData[this.dataCellEditPosition.row][key]);
                conditions.push(`${key} = ${value}`);
            }
            conditions = conditions.join(' AND ');

            let newValue = this.connection.escape(this.dataCellEditValue);
            let sql = `UPDATE ${this.curTable} SET ${this.dataCellEditPosition.col} = ${newValue} WHERE ${conditions} LIMIT 1`;
            console.log(sql);
            try {
                await this.connection.execute(sql);
            } catch ( e ) {
                this.$message.error(e.message);
                return;
            }
            
            await this.tableDataRefresh();
            this.dataCellEditEnable = false;
        }
    },
}
</script>
<style>
.dialog-db-viewer .el-dialog__body {padding-top: 0;}
.dialog-db-viewer .el-form-item {margin-bottom: 0 !important;}
.dialog-db-viewer-condition-text-editor .CodeMirror {height: 100px !important;}
.dialog-db-viewer-order-text-editor .CodeMirror {height: 50px !important;}
</style>
<style scoped>
.block-table-data {width: 100%; overflow: auto;height: 480px;border-top: solid 1px #cbcaca;border-bottom: solid 1px #cbcaca;}
.block-table-data::-webkit-scrollbar { width: 10px; height: 10px; }
.block-table-data::-webkit-scrollbar-thumb {border-radius: 10px;box-shadow: inset 0 0 5px rgba(0,0,0,0.2); background: #535353;}
.block-table-data::-webkit-scrollbar-track {box-shadow: inset 0 0 5px rgba(0,0,0,0.2);border-radius: 10px;background: #EDEDED;}
.table-table-data td, .table-table-data th { white-space: nowrap; }
.table-table-data, .table-table-data tr th, .table-table-data tr td { border: 1px solid #efefef; padding: 2px 10px 2px 5px; }
.table-table-data { border-collapse: collapse;min-width: 100%;}
</style>